# coding: utf-8
# !/usr/bin/python3
from flask import jsonify
import pymysql

import datetime

# 打开数据库连接
db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM students"
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   students = []
   for row in results:
       # 存储数据
       student = {'id':row[0], 'student_name':row[1], 'classname':row[2], 'academy':row[3], 'ml':row[4], 'study':row[5], 'health':row[6], 'basic_score':row[7], 'tech':row[8], 'society':row[9], 'culture':row[10], 'arre':row[11], 'break_rule':row[12], 'fail_number':row[13], 'scholarship_type':row[14], 'apply':row[15], 'modification_time':row[16], 'state':row[17], 'photo':row[18]}
       students.append(student)
except:
   print ("Error: unable to fetch data")
# 关闭数据库连接
db.close()

def check(id):
    data = {}
    code = 500
    for student in students:
        if int(id) == student['id']:
            data['student'] = student
            code = 200
    data['code'] = code
    return jsonify(data)

def accept(id):
    data = {}
    code = 500
    for student in students:
        if int(id) == student['id']:
            numberid = int(id)
            student["state"] = 1
            acc = "UPDATE students SET state=1 WHERE id=%d" % (numberid)
            db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
            cursor = db.cursor()
            try:
                cursor.execute(acc)
                db.commit()
                code = 200
            except:
                db.rollback()
            db.close()
    data['code'] = code
    return jsonify(data)

def reject(id):
    data = {}
    code = 500
    for student in students:
        if int(id) == student['id']:
            numberid = int(id)
            student["state"] = 0
            acc = "UPDATE students SET state=0 WHERE id=%d" % (numberid)
            db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
            cursor = db.cursor()
            try:
                cursor.execute(acc)
                db.commit()
                code = 200
            except:
                db.rollback()
            db.close()
    data['code'] = code
    return jsonify(data)

def checkall():
    data = {}
    data['student'] = []
    code = 500
    for student in students:
        data['student'].append(student)
        code = 200
    data['code'] = code
    return jsonify(data)

def insert_info(info):
    data = {}
    code = 500
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    insert = "INSERT INTO students(id, student_name, classname, academy, ml, study, health, basic_score, tech, society, culture, arre, break_rule, fail_number, scholarship_type, apply, modification_time, state, photo ) \
           VALUES (%d, '%s', '%s', '%s', %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, '%s', '%s', %d,'%s' )" % \
           (info['id'], info['student_name'], info['classname'], info['academy'], info['ml'], info['study'], info['health'], info['basic_score'], info['tech'], info['society'], info['culture'], info['arre'], info['break_rule'], info['fail_number'], info['scholarship_type'], info['apply'], datetime.datetime.now(), info['state'], info['photo'] )
    try:
    # 执行sql语句
       cursor.execute(insert)
    # 执行sql语句
       db.commit()
       code = 200
    except:
    # 发生错误时回滚
       db.rollback()
    db.close()
    student = info
    student['modification_time'] = datetime.datetime.now()
    students.append(student)
    data['code'] = code
    return  jsonify(data)

def search(info):
    data = {}
    code = 500
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # SQL 查询语句
    sql = "SELECT * FROM students"
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        result = cursor.fetchall()
        search_student = []
        for rows in result:
            # 存储数据
            for i in range(len(rows)):
                if info['key'] == str(rows[i]):
                    student_info = {'id': rows[0], 'student_name': rows[1], 'classname': rows[2], 'academy': rows[3], 'ml': rows[4],'study': rows[5], 'health': rows[6], 'basic_score': rows[7], 'tech': rows[8],'society': rows[9], 'culture': rows[10], 'arre': rows[11], 'break_rule': rows[12], 'fail_number': rows[13], 'scholarship_type': rows[14], 'apply': rows[15], 'modification_time': rows[16],'state': rows[17], 'photo': rows[18]}
                    search_student.append(student_info)
                    code = 200
    except:
        print("Error: unable to fetch data")
    # 关闭数据库连接
    db.close()
    data['code'] = code
    data['student'] = search_student
    return jsonify(data)


def scholarship():
    data = {}
    code = 500
    classes = []
    class_group = {}
    class_group['classname'] = "信息安全1701"
    class_group['number'] = 0
    class_group['group'] = []
    class_group['max'] = 0
    class_group['sch_num'] = 0
    classes_group = {}
    classes.append(class_group)
    for student in students:
        state = 0
        for major_class in classes:
            if student['classname'] == major_class['classname']:
                major_class['group'].append(student)
                major_class['number'] = 1 + major_class['number']
                state = 1
        if state == 0:
            classes_group['classname'] = student['classname']
            classes_group['group'] = []
            classes_group['group'].append(student)
            classes_group['number'] = 1
            classes_group['sch_num'] = 0
            classes.append(classes_group)
            print(student['classname'])
            print(classes)

    for i in range(len(classes)):
        classes[i]['max'] = int(classes[i]['number'] * 0.2)
    for i in range(len(classes)):
        if classes[i]['max'] == 0:
            classes[i]['max'] = 1

    student_list = students
    for i in range(len(student_list)):
        for j in range(i, len(student_list)):
            if student_list[i]['study'] < student_list[j]['study']:
                temp = student_list[i]
                student_list[i] = student_list[j]
                student_list[j] = temp
    for student in student_list:
        if student['basic_score'] >= 80 and student['break_rule'] == 0 and student['fail_number'] == 0 and student['scholarship_type'] == 0 and student['arre'] == 0:
            for major_class in classes:
                if major_class['classname'] == student['classname'] and major_class['max'] >= major_class['sch_num']:
                    student['scholarship_type'] = 1
                    major_class['sch_num'] += 1
                    change = "UPDATE students SET scholarship_type=1 WHERE id=%d" % (student['id'])
                    db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
                    cursor = db.cursor()
                    try:
                        cursor.execute(change)
                        db.commit()
                        code = 200
                    except:
                        db.rollback()
                    db.close()

        for i in range(len(student_list)):
            for j in range(i, len(student_list)):
                if student_list[i]['tech'] < student_list[j]['tech']:
                    temp = student_list[i]
                    student_list[i] = student_list[j]
                    student_list[j] = temp
        for student in student_list:
            if student['basic_score'] >= 80 and student['break_rule'] == 0 and student['fail_number'] == 0 and student['scholarship_type'] == 0 and student['arre'] == 0:
                for major_class in classes:
                    if major_class['classname'] == student['classname'] and major_class['max'] >= major_class['sch_num']:
                        student['scholarship_type'] = 2
                        major_class['sch_num'] += 1
                        change = "UPDATE students SET scholarship_type=2 WHERE id=%d" % (student['id'])
                        db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
                        cursor = db.cursor()
                        try:
                            cursor.execute(change)
                            db.commit()
                            code = 200
                        except:
                            db.rollback()
                        db.close()

        for i in range(len(student_list)):
            for j in range(i, len(student_list)):
                if student_list[i]['society'] < student_list[j]['society']:
                    temp = student_list[i]
                    student_list[i] = student_list[j]
                    student_list[j] = temp
        for student in student_list:
            if student['basic_score'] >= 80 and student['break_rule'] == 0 and student['fail_number'] == 0 and student['scholarship_type'] == 0 and student['arre'] == 0:
                for major_class in classes:
                    if major_class['classname'] == student['classname'] and major_class['max'] >= major_class['sch_num']:
                        student['scholarship_type'] = 3
                        major_class['sch_num'] += 1
                        change = "UPDATE students SET scholarship_type=3 WHERE id=%d" % (student['id'])
                        db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
                        cursor = db.cursor()
                        try:
                            cursor.execute(change)
                            db.commit()
                            code = 200
                        except:
                            db.rollback()
                        db.close()

        for i in range(len(student_list)):
            for j in range(i, len(student_list)):
                if student_list[i]['culture'] < student_list[j]['culture']:
                    temp = student_list[i]
                    student_list[i] = student_list[j]
                    student_list[j] = temp
        for student in student_list:
            if student['basic_score'] >= 80 and student['break_rule'] == 0 and student['fail_number'] == 0 and student['scholarship_type'] == 0 and student['arre'] == 0:
                for major_class in classes:
                    if major_class['classname'] == student['classname'] and major_class['max'] >= major_class['sch_num']:
                        student['scholarship_type'] = 4
                        major_class['sch_num'] += 1
                        change = "UPDATE students SET scholarship_type=4 WHERE id=%d" % (student['id'])
                        db = pymysql.connect("localhost", "root", "ha<06vSk:gj(", "test")
                        cursor = db.cursor()
                        try:
                            cursor.execute(change)
                            db.commit()
                            code = 200
                        except:
                            db.rollback()
                        db.close()

    data['student'] = student_list
    data['code'] = code
    return jsonify(data)





